package com.wuseguang.springcloud.JdbcTemplate;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.*;


/**
 * @描述 jdbc (注意事项，查询时如果结果为空会出现异常,需要进行异常处理)
 * @创建人 zm
 * @创建时间 2019/11/16
 */
@Repository
public class BaseTemplate {
    private Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired(required = false)
    public JdbcTemplate dao;


    /**
     * 查询list集合
     *
     * @param listSql   sql
     * @param rowMapper 返回类型
     * @return
     */
    public List<?> queryList(String listSql, RowMapper<?> rowMapper) {
        return dao.query(listSql, rowMapper);
    }

    /**
     * 查询list集合
     *
     * @param listSql   sql
     * @param rowMapper 返回类型
     * @param obj       参数
     * @return
     */
    public List<?> queryList(String listSql, RowMapper<?> rowMapper, Object[] obj) {
        return dao.query(listSql, rowMapper, obj);
    }


    /**
     * 查询一个对象
     *
     * @param sql
     * @param c   对象
     * @return
     */
    public Object queryObjectBySql(String sql, Class c) {
        return dao.queryForObject(sql, c);
    }

    /**
     * 根据条件查询对象
     *
     * @param sql
     * @param c    对象
     * @param args 参数
     * @return
     */
    public Object queryObjectByParameter(String sql, Class c, Object... args) {
        return dao.queryForObject(sql, args, c);
    }


    /**
     * 根据条件修改
     *
     * @param sql
     * @param args
     * @return
     */
    public int updateByParameter(String sql, Object... args) {
        return dao.update(sql, args);
    }

    /**
     * 查询list<String>
     *
     * @param sql
     * @param obj
     * @return
     */
    public List<String> queryListString(String sql, Object[] obj) {
        return dao.queryForList(sql, obj, String.class);
    }

    /**
     * 查询list<Integer>
     *
     * @param sql
     * @param obj
     * @return
     */
    public List<Integer> queryListInteger(String sql, Object[] obj) {
        return dao.queryForList(sql, obj, Integer.class);
    }

    /**
     * 根据参数获取map
     *
     * @param sql
     * @param key
     * @param value
     * @return
     */
    public Map<String, String> getMap(String sql, String key, String value) {
        return dao.query(sql, rs -> {
            Map<String, String> map = new HashMap<>();
            while (rs.next()) {
                map.put(rs.getString(key), rs.getString(value));
            }
            return map;
        });
    }

    /**
     * 查询map集合
     *
     * @param sql
     * @param arg
     * @return
     */
    public Map<String, Object> queryMap(String sql, Object... arg) {
        return dao.queryForMap(sql, arg);
    }

    /**
     * 查询list<map<string,object>>
     *
     * @param sql
     * @param args
     * @return
     */
    public List<Map<String, Object>> queryListMap(String sql, Object... args) {
        return dao.queryForList(sql, args);
    }

    /**
     * 修改完之后返回id
     *
     * @param sql
     * @param args
     * @return
     */
    public Long updateByParameterGetId(String sql, Object... args) {
        dao.update(sql, args);
        return dao.queryForObject("select last_insert_id() as aa", Long.class);
    }

    public Object query(String sql, RowMapper<?> rowMapper, Object... args) {
        return dao.queryForObject(sql, rowMapper, args);
    }


    /**
     * 根据条件查询返回string
     *
     * @param sql
     * @param args 条件
     * @return
     */
    public String queryString(String sql, Object... args) {
        return dao.query(sql, args, rs -> {

            String string = "";
            if (rs.next()) {
                string = rs.getString("string");
            }
            return string;
        });
    }

    /**
     * @描述 根据条件查询返回string
     * @创建人 zm
     * @创建时间 2020/5/7
     */
    public Integer queryInteger(String sql, Object... args) {
        return dao.query(sql, args, rs -> {

            Integer integer = 0;
            if (rs.next()) {
                integer = rs.getInt("ints");
            }
            return integer;
        });
    }

    /**
     * 根据条件返回double
     *
     * @param sql
     * @param args
     * @return
     */
    public double queryBouble(String sql, Object... args) {
        return dao.query(sql, args, rs -> {

            double integer = 0.0;
            if (rs.next()) {
                integer = rs.getDouble("doubles");
            }
            return integer;
        });
    }


    /**
     * 查询条数
     *
     * @param sql
     * @param args
     * @return
     */
    public Integer queryCount(String sql, Object... args) {
        return dao.query(sql, args, rs -> {

            Integer count = 0;
            if (rs.next()) {
                count = rs.getInt("count");
            }
            return count;
        });
    }

    /**
     * 查询年级
     *
     * @param teacherId 教师id
     * @param type      数据类型 1 本部门及下属部门，2全部数据
     * @return
     */
    public List<Map<String, Object>> queruGradeList(String teacherId, int type, String schoolId) {

        logger.info("查询年级");

        if (type == 1) {
            logger.info("本部门及下属部门");
            return queruGradeListTypeBy1(teacherId, schoolId);
        }

        return queruGradeListTypeBy2(schoolId);
    }


    public List<Map<String, Object>> queruGradeListTypeBy1(String teacherId, String schoolId) {
        //全部集合
        List<Map<String, Object>> list = new ArrayList<>();

        //查询这个老师是否有最高部门的角色（应为所有最高部门都能查看所有年级的）
        String sql = "select count(1) count from teacher_role where teacher_id=? and type=1";
        int count = queryCount(sql, teacherId);
        //判断是否有最高部门
        if (count > 0) {
            logger.info("存在最高部门");
            return queruGradeListTypeBy2(schoolId);
        }
        logger.info("不存在存在最高部门");

        //查询这个老师是否有年级部门的角色(查询出来的是年级id)
        sql = " SELECT id,name,school_id,department_id FROM grade g WHERE EXISTS(SELECT * FROM classes c WHERE  " +
                "EXISTS(SELECT * FROM teacher_role tr,roles r WHERE tr.`role_id`=r.id AND r.id=2 AND c.id=tr.pid AND tr.teacher_id=? AND tr.type=3 GROUP BY tr.pid) " +
                "  AND c.grade_id = g.id)  UNION  select id,name,school_id,department_id from grade g where EXISTS(select * from teacher_role tr where g.id=tr.pid and  tr.teacher_id= ? and tr.type=2 GROUP BY tr.pid) GROUP BY id desc UNION " +
                " select id,name,school_id,department_id from grade g where EXISTS(select * from classes c where  EXISTS(select * from teacher_role tr where c.id=tr.pid and tr.teacher_id= ? and tr.type=3 GROUP BY tr.pid) and c.grade_id = g.id) GROUP BY id desc";

        try {
            list = queryListMap(sql, teacherId, teacherId, teacherId);
        } catch (Exception e) {
            logger.info("该教师没有授课班级查询不了年级");
        }

        return list;
    }


    public List<Map<String, Object>> queruGradeListTypeBy2(String schoolId) {
        //查询所有年级
        String sql = "select * from grade where school_id = ? GROUP BY id  ORDER BY other,education_id,id";

        //全部集合
        List<Map<String, Object>> list = queryListMap(sql, schoolId);

        return list;
    }

    /**
     * 查询老师所拥有的班级数据权限
     *
     * @param gradeId  年级id
     * @param type     数据类型 1 本部门及下属部门，2全部数据
     * @param schoolId 学校id
     * @return
     */
    public List<Map<String, Object>> queryClassList(String gradeId, int type, String schoolId, String teacherId) {

        logger.info("查询教师有权限的班级");

        if (type == 1) {
            logger.info("本部门及下属部门");
            return queryClassListTypeBy1(gradeId, schoolId, teacherId);
        }

        return queryClassListTypeBy2(gradeId, schoolId);
    }

    /**
     * 查询本部门及下属部门的班级
     *
     * @param gradeId  年级id
     * @param schoolId 学校id
     * @return
     */
    private List<Map<String, Object>> queryClassListTypeBy1(String gradeId, String schoolId, String teacherId) {
        //全部集合
        List<Map<String, Object>> list = new ArrayList<>();

        //查询这个老师是否有最高部门的角色（应为所有最高部门都能查看所有班级级的）
        String sql = "select count(1) count from teacher_role where teacher_id=? and type=1";
        int count = queryCount(sql, teacherId);
        //判断是否有最高部门
        if (count > 0) {
            logger.info("存在最高部门");
            return queryClassListTypeBy2(gradeId, schoolId);
        }
        logger.info("不存在存在最高部门");

        //查询这个老师是否有年级部门的角色(查询出来的是年级id)
        sql = "    SELECT c.id, c.name,c.school_id FROM classes c  WHERE  EXISTS(SELECT * FROM teacher_role tr ,roles r  " +
                "  WHERE tr.role_id=r.id AND r.id=2 AND c.id=tr.pid AND tr.teacher_id=? AND tr.type=3 GROUP BY tr.pid) AND c.grade_id=? AND graduation =0 GROUP BY c.id DESC " +
                "  UNION  select c.id, c.name,c.school_id  from classes c  where EXISTS(select * from teacher_role tr " +
                " where c.grade_id=tr.pid and  tr.teacher_id= ? and tr.type=2 GROUP BY tr.pid) and c.grade_id=? and graduation =0  GROUP BY c.id desc" +
                " UNION " +
                " select c.id, c.name,c.school_id from classes c  where  EXISTS(select * from teacher_role tr " +
                " where c.id=tr.pid and tr.teacher_id= ? and tr.type=3 GROUP BY tr.pid) and c.grade_id=? and graduation =0 GROUP BY c.id desc";

        try {
            list = queryListMap(sql, teacherId, gradeId, teacherId, gradeId, teacherId, gradeId);
        } catch (Exception e) {
            logger.info("该教师没有授课班级");
        }

        return list;
    }

    /**
     * 查询全部班级
     *
     * @param gradeId
     * @param schoolId
     * @return
     */
    private List<Map<String, Object>> queryClassListTypeBy2(String gradeId, String schoolId) {

        //查询班级sql
        String sql = "select c.id, c.name,c.school_id from classes c  where c.school_id =? and c.grade_id=? and graduation !=1";

        List<Map<String, Object>> list = new ArrayList<>();

        try {
            list = queryListMap(sql, schoolId, gradeId);
        } catch (Exception e) {
            logger.info("该教师没有授课班级");
        }

        return list;
    }

    /**
     * 查询map集合，根据某个值做key
     *
     * @param sql
     * @param id   做key名字
     * @param name 做value得名字
     * @param args sql 参数
     * @return
     */
    public Map<String, Object> queryIdMap(String sql, String id, String name, Object... args) {


        return dao.query(sql, args, rs -> {

            Map<String, Object> map = new HashMap<String, Object>();
            while (rs.next()) {
                map.put(rs.getString(id), rs.getObject(name));
            }
            return map;
        });
    }

    /**
     * 修改心跳
     *
     * @param field   要修改的字段
     * @param classid 要修改的班级
     * @return
     */
    public int updateBancardFlag(String field, String classid) {
        logger.info("field = {} classid = {}", field, classid);
        String sql = "UPDATE `cloud_window`.`bancard` SET ? = 1 WHERE `classroomid` =(select id  from classroom where classid= ? )";
        return dao.update(sql, field, classid);
    }

    /**
     * 查询这个老师是否是年级组长
     *
     * @param teacherId
     * @return
     */
    public List<String> findGradeLeader(String teacherId) {
        //这个这个老师是否是年级组长
        String sql = "select pid from teacher_role where teacher_id = ? and type =2 ";

        return dao.query(sql, new Object[]{teacherId}, rs -> {
            //创建list
            List<String> list = new ArrayList<>();
            while (rs.next()) {
                //赋值
                list.add(rs.getString("pid"));
            }
            return list;
        });
    }

    /**
     * 查询教师的数据权限
     *
     * @param teacherId
     * @return
     */
    public int findDatePermissions(String teacherId) {


        //查询数据权限sql
        String sql = "select date_permissions_id id from role_data_permissions rdp where EXISTS (SELECT * FROM teacher_role tr LEFT JOIN roles r on (r.id=tr.role_id) WHERE r.grouping_id = rdp.role_id AND tr.teacher_id =?) GROUP BY date_permissions_id ORDER BY date_permissions_id LIMIT 1";


        return dao.query(sql, new Object[]{teacherId}, rs -> {
            if (rs.next()) {
                return rs.getInt("id");
            }
            return 1;
        });
    }

    /**
     * @描述 查询设备信息
     * @创建人 zm
     * @创建时间 2020/2/14
     */
    public Map<String, Object> queryDevice(String deviceid) {
        String sql = "SELECT b.id,b.deviceid,b.classroomid,m.classid,s.name classname,s.class_icon,s.class_teacher_id,l.id school_id,l.school_name,r.name teachername,r.head_portrait,b.clientid,b.deviceType,b.pstatus ,s.grade_id FROM " +
                " bancard b,classroom m,school l ,classes s LEFT JOIN teacher r ON s.class_teacher_id=r.id WHERE b.deviceid=? AND  b.classroomid=m.id AND s.id=m.classid " +
                " AND b.school_id=m.school_id AND s.school_id=m.school_id AND s.school_id=l.id LIMIT 1";
        Map<String, Object> stringObjectMap = null;
        try {
            stringObjectMap = queryMap(sql, deviceid);
        } catch (Exception e) {
            logger.info("设备未绑定 queryDevice-->error={}", e);
        }
        logger.info("queryDevice-->stringObjectMap={}", stringObjectMap);
        return stringObjectMap;
    }

    /**
     * 查询教师的数据权限班级并且拼接成String类型
     *
     * @param schoolId  学校id
     * @param teacherId 教师id
     * @return
     */
    public String findClassIds(String schoolId, String teacherId) {

        StringBuilder classIds = new StringBuilder();
        //查询教师的数据权限
        int type = findDatePermissions(teacherId);
        //查询教师的年级
        List<Map<String, Object>> list = queruGradeList(teacherId, type, schoolId);
        //循环年级
        for (Map<String, Object> map : list) {
            //查询教师的班级
            List<Map<String, Object>> classList = queryClassList(String.valueOf(map.get("id")), type, schoolId, teacherId);
            //循环班级
            for (Map<String, Object> classMap : classList) {
                classIds.append(classMap.get("id")).append(",");
            }
        }
        String classid = classIds.toString();
        //返回班级信息
        return classid.substring(0, classid.length() - 1);
    }

    /**
     * 根据开发者id查找token
     *
     * @param id 开发者id
     * @return
     */
    public Map<String, Object> getAppletAccessToken(Integer id) {
        //查询sql
        String sql = "select id,access_token,expires_in,acquisition_time from we_chat_access_token where developer_id =?";

        Map<String, Object> tokenMap = new HashMap<>();
        try {
            tokenMap = queryMap(sql, id);
        } catch (Exception e) {
            logger.info("没有token");
        }
        return tokenMap;
    }

    /**
     * 插入新的accessToken
     *
     * @param developingId 开发者id
     * @param expiresIn    获取的有效期
     * @param token        token
     * @return
     */
    public int insertAccessToken(Integer developingId, String expiresIn, String token) {

        //插入sql
        String sql = "INSERT INTO `we_chat_access_token`(`access_token`, `expires_in`, acquisition_time, `developer_id`) " +
                "VALUES (?, ?,now(), ?)";
        updateByParameter(sql, token, expiresIn, developingId);
        return 1;
    }

    /**
     * 修改过期的accessToken
     *
     * @param id        tokenid
     * @param expiresIn 有效期
     * @param token     token
     * @return
     */
    public int updateAccessToken(Integer id, String expiresIn, String token) {

        //插入sql
        String sql = "UPDATE `we_chat_access_token` SET `access_token` = ?, `expires_in` = ?, `acquisition_time` = now() WHERE `id` = ?";
        updateByParameter(sql, token, expiresIn, id);
        return 1;
    }


    public int updateSubscribe(String openId, String templateId) {
        //插入sql
        String sql = "UPDATE `user_subscription_template` ust " +
                "LEFT JOIN we_chat_template wct on(ust.template_id=wct.id) " +
                "SET ust.`state` = 1 WHERE wct.template_id =? and ust.open_id = ?";
        updateByParameter(sql, templateId, openId);

        return 1;
    }
}
